from datetime import datetime
from itertools import chain
import pandas as pd
from sqlalchemy import func, or_, and_, exc, select, between, text
from collections import OrderedDict

from app.config.db import DBUtils
from app.model.BusinessInfoModel import BusinessInfo
from app.model.FKListModel import fklist
from app.model.SKListModel import sklist


class TransactionFeatures:
    def __init__(self):
        self.db_utils = DBUtils()

    def select_trading_indicators_data(self, session, companyName, company_type, year):

        now = datetime.now()
        current_year = now.year
        if year and int(year) != int(current_year):
            year = int(year)
            first_month = f"{year}-01"
            last_month = f"{year}-12"
        else:
            year = int(current_year)
            first_month = f"{current_year}-01"
            last_month = now.strftime("%Y-%m")

        if company_type == 1:
            query = text(f"""
                        SELECT FKZHMC, SKZHMC, FKJE, SCRQ, JYND, JS_YWFL , RQ, 'FK' AS LX
                        FROM ESUNTEST.HX_BI_FKLIST
                        WHERE FKZHMC = :param1 and JS_YWFL != '同名户结算' and JYND = :param2
                        UNION ALL
                        SELECT FKZHMC, SKZHMC, FKJE, SCRQ, JYND, JS_YWFL , RQ, 'SK' AS LX
                        FROM ESUNTEST.HX_BI_SKLIST
                        WHERE SKZHMC = :param1 and JS_YWFL != '同名户结算' and JYND = :param2
                    """)
            datalist = list(session.execute(query, {'param1': companyName, 'param2': year}).mappings())
        elif company_type == 2:
            query = text(f"""
                        SELECT FKZHMC, SKZHMC, FKJE, SCRQ, JYND, JS_YWFL , RQ, 'FK' AS LX, FKGS AS GS
                        FROM ESUNTEST.HX_BI_FKLIST
                        WHERE FKGS = :param1 and JYND = :param2
                        UNION ALL
                        SELECT FKZHMC, SKZHMC, FKJE, SCRQ, JYND, JS_YWFL , RQ, 'SK' AS LX, SKGS AS GS
                        FROM ESUNTEST.HX_BI_SKLIST
                        WHERE SKGS = :param1 and JYND = :param2
                    """)
            datalist = list(session.execute(query, {'param1': companyName, 'param2': year}).mappings())
        elif company_type == 3:
            query = text(f"""
                        SELECT FKZHMC, SKZHMC, FKJE, SCRQ, JYND, JS_YWFL , RQ, 'FK' AS LX
                        FROM ESUNTEST.HX_BI_FKLIST
                        WHERE JYND = :param2
                        UNION ALL
                        SELECT FKZHMC, SKZHMC, FKJE, SCRQ, JYND, JS_YWFL , RQ, 'SK' AS LX
                        FROM ESUNTEST.HX_BI_SKLIST
                        WHERE JYND = :param2
                    """)
            datalist = list(session.execute(query, {'param2': year}).mappings())
        else:
            query = text(f"""
                        SELECT FKZHMC, SKZHMC, FKJE, SCRQ, JYND, JS_YWFL , RQ, 'FK' AS LX
                        FROM ESUNTEST.HX_BI_FKLIST
                        WHERE SKZHMC = :param1 and JS_YWFL != '同名户结算' and JYND = :param2
                        UNION ALL
                        SELECT FKZHMC, SKZHMC, FKJE, SCRQ, JYND, JS_YWFL , RQ, 'SK' AS LX
                        FROM ESUNTEST.HX_BI_SKLIST
                        WHERE FKZHMC = :param1 and JS_YWFL != '同名户结算' and JYND = :param2
                    """)
            datalist = list(session.execute(query, {'param1': companyName, 'param2': year}).mappings())
        if not datalist or not isinstance(datalist, list) or len(datalist) == 0:
            return {
                "jytj": {
                    "sy_jy_list": [{str(i).zfill(2): 0} for i in range(1, 13)],
                    "xy_jy_list": [{str(i).zfill(2): 0} for i in range(1, 13)]
                },
                "jyzb": None
            }
        df = pd.DataFrame.from_records(datalist).rename(columns=str.upper)
        # 根据company_type分割上游和下游数据
        if company_type == 1:
            up_df = df[(df['LX'] == 'FK') & (df['FKZHMC'] == companyName)]
            down_df = df[(df['LX'] == 'SK') & (df['SKZHMC'] == companyName)]
        elif company_type == 2:
            up_df = df[(df['LX'] == 'FK') & (df['GS'] == companyName)]
            down_df = df[(df['LX'] == 'SK') & (df['GS'] == companyName)]
        elif company_type == 3:
            up_df = df[(df['LX'] == 'FK')]
            down_df = df[(df['LX'] == 'SK')]
        else:
            up_df = df[(df['LX'] == 'SK') & (df['FKZHMC'] == companyName)]
            down_df = df[(df['LX'] == 'FK') & (df['SKZHMC'] == companyName)]

        # 计算总交易额和笔数
        up_total = up_df['FKJE'].sum()
        up_cnt = len(up_df)
        down_total = down_df['FKJE'].sum()
        down_cnt = len(down_df)
        total_trade = up_total + down_total

        # 年初年末交易额
        up_begin_year = up_df[up_df['RQ'] == first_month]['FKJE'].sum()
        down_begin_year = down_df[down_df['RQ'] == first_month]['FKJE'].sum()
        up_end_year = up_df[up_df['RQ'] == last_month]['FKJE'].sum()
        down_end_year = down_df[down_df['RQ'] == last_month]['FKJE'].sum()

        # 月初月末交易额
        up_begin_month = up_df[up_df['SCRQ'].dt.day.between(1, 5)]['FKJE'].sum()
        down_begin_month = down_df[down_df['SCRQ'].dt.day.between(1, 5)]['FKJE'].sum()

        # 计算月末条件
        def is_month_end(date):
            return date.day >= (date.days_in_month - 4)

        if up_df.empty:
            up_end_month = 0
        else:
            up_end_month = up_df[up_df['SCRQ'].apply(is_month_end)]['FKJE'].sum()

        if down_df.empty:
            down_end_month = 0
        else:
            down_end_month = down_df[down_df['SCRQ'].apply(is_month_end)]['FKJE'].sum()

        # 计算各占比
        begin_mon_per = f"{(up_begin_month + down_begin_month) / total_trade * 100:.2f}%"
        end_mon_per = f"{(up_end_month + down_end_month) / total_trade * 100:.2f}%"
        begin_year_per = f"{(up_begin_year + down_begin_year) / total_trade * 100:.2f}%"
        end_year_per = f"{(up_end_year + down_end_year) / total_trade * 100:.2f}%"

        # 按月统计交易情况
        up_monthly = up_df.groupby('RQ', as_index=False).agg(total=('FKJE', 'sum'), cnt=('FKJE', 'count'))
        down_monthly = down_df.groupby('RQ', as_index=False).agg(total=('FKJE', 'sum'), cnt=('FKJE', 'count'))
        combined = pd.concat([up_monthly, down_monthly])
        group_mon = combined.groupby('RQ', as_index=False).agg(total=('total', 'sum'), cnt=('cnt', 'sum'))
        if not group_mon.empty:
            max_month = group_mon.loc[group_mon['cnt'].idxmax()]
            trade_mon_per = f"{max_month['total'] / total_trade * 100:.2f}%"
            max_month_rq = max_month['RQ']
        else:
            trade_mon_per = "0.00%"
            max_month_rq = ""

        # 生成月度趋势数据
        all_months = pd.date_range(f'{year}-01-01', periods=12, freq='MS').strftime('%Y-%m')
        up_months = (up_df.groupby('RQ')['FKJE'].sum()
                     .reindex(all_months, fill_value=0)
                     .reset_index()
                     .rename(columns={'index': 'RQ'}))
        up_months['month'] = up_months['RQ'].str.split('-').str[1]
        up_stream = [{row['month']: row['FKJE']} for _, row in up_months.iterrows()]

        down_months = (down_df.groupby('RQ')['FKJE'].sum()
                       .reindex(all_months, fill_value=0)
                       .reset_index()
                       .rename(columns={'index': 'RQ'}))
        down_months['month'] = down_months['RQ'].str.split('-').str[1]
        down_stream = [{row['month']: row['FKJE']} for _, row in down_months.iterrows()]

        # 处理金额显示
        def format_amt(amt):
            return round(amt / 10000, 2) if amt >= 50 else 0.01 if amt > 0 else 0.0

        sy_amt = format_amt(up_total)
        xy_amt = format_amt(down_total)

        result = {
            "jytj": {"sy_jy_list": up_stream, "xy_jy_list": down_stream},
            "jyzb": {
                "yc_jy_ratio": begin_mon_per,
                "ym_jy_ratio": end_mon_per,
                "nc_jy_ratio": begin_year_per,
                "nm_jy_ratio": end_year_per,
                "sy_total_amount": sy_amt,
                "xy_total_amount": xy_amt,
                "sy_total_count": up_cnt,
                "xy_total_count": down_cnt,
                "sxy_jy_max_month": max_month_rq,
                "max_month_jy_ratio": trade_mon_per
            }
        }
        return result

    # 交易指标查询
    # def select_trading_indicators_data(self, session, companyName, company_type, year):
    #     now = datetime.now()
    #     if year and year != now.year:
    #         year = int(year)
    #         first_month = str(year) + '-' + '01'
    #         last_month = str(year) + '-' + '12'
    #     else:
    #         year = int(now.year)
    #         first_month = str(now.year) + '-' + '01'  # 年初
    #         last_month = now.strftime('%Y-%m')  # 年末
    #     fk_begin_day = func.day(fklist.SCRQ).between(1, 5) #付款月初
    #     sk_begin_day = func.day(sklist.SCRQ).between(1, 5) #收款月初
    #     fk_end_day = fklist.SCRQ >= func.trunc(func.last_day(fklist.SCRQ)) - 4 #付款月末
    #     sk_end_day = sklist.SCRQ >= func.trunc(func.last_day(sklist.SCRQ)) - 4 #收款月末
    #     fkje = func.nvl(func.sum(fklist.FKJE), 0)  # 付款总金额
    #     skje = func.nvl(func.sum(sklist.FKJE), 0)  # 收款总金额
    #     fk_count = func.count(fklist.FKJE) # 付款计数
    #     sk_count = func.count(sklist.FKJE) # 收款计数
    #     fk_year = fklist.JYND == year # 付款表年度条件筛选
    #     sk_year = sklist.JYND == year # 收款表年度条件筛选
    #     fk_dif_name = fklist.JS_YWFL != '同名户结算'  # 付款表排除同名户
    #     sk_dif_name = sklist.JS_YWFL != '同名户结算'  # 收款表排除同名户
    #     up_cy_name = fklist.FKZHMC == companyName  # CYDW上游付款条件
    #     down_cy_name = sklist.SKZHMC == companyName  # CYDW下游收款条件
    #     up_wl_name = sklist.FKZHMC == companyName  # WLDW上游收款条件
    #     down_wl_name = fklist.SKZHMC == companyName  # WLDW下游付款条件
    #
    #     if company_type == 1: #当为成员单位时
    #         try:
    #             # 上游交易总额
    #             up_total = session.execute(
    #                 select(fkje.label('total'), fk_count.label('cnt')).where(and_(up_cy_name, fk_dif_name, fk_year))).first()
    #             # 下游交易总额
    #             # stmt = select(skje.label('total'), sk_count.label('cnt')).where(and_(down_cy_name, sk_dif_name, sk_year))
    #             # print(stmt.compile(compile_kwargs={"literal_binds": True}))
    #             down_total = session.execute(
    #                 select(skje.label('total'), sk_count.label('cnt')).where(and_(down_cy_name, sk_dif_name, sk_year))).first()
    #             # 上游年初交易
    #             up_begin_year = session.execute(
    #                 select(fkje).where(and_(up_cy_name, fk_dif_name, fklist.RQ == first_month))
    #             ).scalar_one_or_none()
    #             # 下游年初交易
    #             down_begin_year = session.execute(
    #                 select(skje).where(and_(down_cy_name, sk_dif_name, sklist.RQ == first_month))
    #             ).scalar_one_or_none()
    #             # 上游年末交易
    #             up_end_year = session.execute(
    #                 select(fkje).where(and_(up_cy_name, fk_dif_name, fklist.RQ == last_month))
    #             ).scalar_one_or_none()
    #             # 下游年末交易
    #             down_end_year = session.execute(
    #                 select(skje).where(and_(down_cy_name, sk_dif_name, sklist.RQ == last_month))
    #             ).scalar_one_or_none()
    #             # 上游月初交易
    #             up_begin_month = session.execute(
    #                 select(fkje).where(and_(up_cy_name, fk_dif_name, fk_year, fk_begin_day))
    #             ).scalar_one_or_none()
    #             # 下游月初交易
    #             down_begin_month = session.execute(
    #                 select(skje).where(and_(down_cy_name, sk_dif_name, sk_year, sk_begin_day))
    #             ).scalar_one_or_none()
    #             # 上游月末交易
    #             up_end_month = session.execute(
    #                 select(fkje).where(and_(up_cy_name, fk_dif_name, fk_end_day, fk_year))
    #             ).scalar_one_or_none()
    #             # 下游月末交易
    #             down_end_month = session.execute(
    #                 select(skje).where(and_(down_cy_name, sk_dif_name, sk_end_day, sk_year))
    #             ).scalar_one_or_none()
    #             # 上游交易按月统计
    #             up_every_month = session.execute(
    #                 select(fkje.label('total'), fk_count.label('cnt'), fklist.RQ)
    #                 .where(and_(up_cy_name, fk_dif_name, fk_year))
    #                 .group_by(fklist.RQ).order_by(fk_count.desc())).mappings().all()
    #             # 下游交易按月统计
    #             down_every_month = session.execute(
    #                 select(skje.label('total'), sk_count.label('cnt'), sklist.RQ)
    #                 .where(and_(down_cy_name, sk_dif_name, sk_year))
    #                 .group_by(sklist.RQ).order_by(sk_count.desc())).mappings().all()
    #         except exc.SQLAlchemyError as e:
    #             error_response = [{'error': str(e)}]
    #             try:
    #                 self.db_utils.rollback()
    #             except Exception as rollback_error:
    #                 print(f"Failed to rollback transaction: {rollback_error}")
    #             return error_response
    #         finally:
    #             try:
    #                 self.db_utils.remove_session()
    #             except Exception as remove_error:
    #                 print(f"Failed to rollback transaction: {remove_error}")
    #     else: # 当为往来单位时
    #         try:
    #             # 上游交易总额
    #             up_total = session.execute(
    #                 select(skje.label('total'), sk_count.label('cnt')).where(up_wl_name, sk_dif_name, sk_year)).first()
    #             # 下游交易总额
    #             down_total = session.execute(
    #                 select(fkje.label('total'), fk_count.label('cnt')).where(down_wl_name, fk_dif_name, fk_year)).first()
    #             # 上游年初交易
    #             up_begin_year = session.execute(
    #                 select(skje).where(and_(up_wl_name, sk_dif_name, sklist.RQ == first_month))
    #             ).scalar_one_or_none()
    #             # 下游年初交易
    #             down_begin_year = session.execute(
    #                 select(fkje).where(and_(down_wl_name, fk_dif_name, fklist.RQ == first_month))
    #             ).scalar_one_or_none()
    #             # 上游年末交易
    #             up_end_year = session.execute(
    #                 select(skje).where(and_(up_wl_name, sk_dif_name, sklist.RQ == last_month))
    #             ).scalar_one_or_none()
    #             # 下游年末交易
    #             down_end_year = session.execute(
    #                 select(fkje).where(and_(down_wl_name, fk_dif_name, fklist.RQ == last_month))
    #             ).scalar_one_or_none()
    #             # 上游月初交易
    #             up_begin_month = session.execute(
    #                 select(skje).where(and_(up_wl_name, sk_dif_name, sk_begin_day, sk_year))
    #             ).scalar_one_or_none()
    #             # 下游月初交易
    #             down_begin_month = session.execute(
    #                 select(fkje).where(and_(down_wl_name, fk_dif_name, fk_begin_day, fk_year))
    #             ).scalar_one_or_none()
    #             # 上游月末交易
    #             up_end_month = session.execute(
    #                 select(skje).where(and_(up_wl_name, sk_dif_name, sk_end_day, sk_year))
    #             ).scalar_one_or_none()
    #             # 下游月末交易
    #             down_end_month = session.execute(
    #                 select(fkje).where(and_(down_wl_name, fk_dif_name, fk_end_day, fk_year))
    #             ).scalar_one_or_none()
    #             # 上游交易按月统计
    #             up_every_month = session.execute(
    #                 select(skje.label('total'), sk_count.label('cnt'), sklist.RQ)
    #                 .where(and_(up_wl_name, sk_dif_name, sk_year))
    #                 .group_by(sklist.RQ).order_by(sk_count.desc())).mappings().all()
    #             # 下游交易按月统计
    #             down_every_month = session.execute(
    #                 select(fkje.label('total'), fk_count.label('cnt'), fklist.RQ)
    #                 .where(and_(down_wl_name, fk_dif_name, fk_year))
    #                 .group_by(fklist.RQ).order_by(fk_count.desc())).mappings().all()
    #         except exc.SQLAlchemyError as e:
    #             error_response = [{'error': str(e)}]
    #             try:
    #                 self.db_utils.rollback()
    #             except Exception as rollback_error:
    #                 print(f"Failed to rollback transaction: {rollback_error}")
    #             return error_response
    #         finally:
    #             try:
    #                 self.db_utils.remove_session()
    #             except Exception as remove_error:
    #                 print(f"Failed to rollback transaction: {remove_error}")
    #
    #     if up_total and up_total.total != 0 and down_total and down_total.total != 0:
    #         # 交易总额
    #         trade_total = float(up_total.total) + float(down_total.total)
    #         # 上游交易总额，总笔数
    #         up_amount = round(float(up_total.total) / 10000, 2) if up_total.total >= 50 or up_total.total == 0 else 0.01
    #         up_cnt = int(up_total.cnt)
    #         # 下游交易总额，总笔数
    #         down_amount = round(float(down_total.total) / 10000, 2) if down_total.total >= 50 or down_total.total == 0 else 0.01
    #         down_cnt = down_total.cnt
    #         # 月初交易占比
    #         begin_month = ((float(up_begin_month) + float(down_begin_month)) / trade_total) * 100
    #         begin_mon_per = f"{max(begin_month, 0.01):.2f}%"
    #         # 月末交易占比
    #         end_month = ((float(up_end_month) + float(down_end_month)) / trade_total) * 100
    #         end_mon_per = f"{max(end_month, 0.01):.2f}%"
    #         # 年初交易占比
    #         begin_year = ((float(up_begin_year) + float(down_begin_year)) / trade_total) * 100
    #         begin_year_per = f"{max(begin_year, 0.01):.2f}%"
    #         # 年末交易占比
    #         end_year = ((float(up_end_year) + float(down_end_year)) / trade_total) * 100
    #         end_year_per = f"{max(end_year, 0.01):.2f}%"
    #         # 交易最频繁月
    #         # trade_combine = defaultdict(lambda:{'count':0, 'sum':0})
    #         # for row in chain(up_every_month, down_every_month):
    #         #     month = row['RQ']
    #         #     trade_combine['RQ']['count'] += row['cnt']
    #         #     trade_combine['RQ']['sum'] += row['total']
    #         # (max_cnt, max_month) = max(trade_combine.items(), key=lambda x: x[1]['count'])
    #         # print(max_cnt, max_month)
    #         # max_month = trade_combine[max_cnt]
    #         df_up = pd.DataFrame(up_every_month)
    #         df_down = pd.DataFrame(down_every_month)
    #         combined_df = pd.concat([df_up, df_down], ignore_index=True)
    #         group_mon = combined_df.groupby('RQ', as_index=False).sum()
    #         # 交易最频繁的月
    #         max_month = group_mon.loc[group_mon['cnt'].idxmax()]
    #         # 占全年交易额
    #         max_fkje = (float(max_month['total']) / trade_total) * 100
    #         trade_mon_per = f"{max(max_fkje, 0.01):.2f}%"
    #
    #         up_months = OrderedDict((f"{m:02d}", 0) for m in range(1, 13))
    #         for row in up_every_month:
    #             month = row['RQ'].split('-')[1]
    #             if month in up_months:
    #                 up_months[month] = row['total']
    #         up_stream = [{
    #             month: total
    #         } for month, total in up_months.items()]
    #         # up_stream = [{
    #         #     'month': month,
    #         #     'total': total
    #         # } for month, total in up_months.items()]
    #
    #         down_months = OrderedDict((f"{m:02d}", 0) for m in range(1, 13))
    #         for row in down_every_month:
    #             month = row['RQ'].split('-')[1]
    #             if month in down_months:
    #                 down_months[month] = row['total']
    #         # down_stream = [{
    #         #     'month': month,
    #         #     'total': total
    #         # } for month, total in down_months.items()]
    #         down_stream = [{
    #             month: total
    #         } for month, total in down_months.items()]
    #
    #         statistics = {
    #             'sy_jy_list': up_stream,
    #             'xy_jy_list': down_stream
    #         }
    #         index = {
    #             'yc_jy_ratio' : begin_mon_per,
    #             'ym_jy_ratio' : end_mon_per,
    #             'nc_jy_ratio' : begin_year_per,
    #             'nm_jy_ratio' : end_year_per,
    #             'sy_total_amount' : up_amount,
    #             'xy_total_amount' : down_amount,
    #             'sy_total_count' : up_cnt,
    #             'xy_total_count' : down_cnt,
    #             'sxy_jy_max_month' : max_month['RQ'],
    #             'max_month_jy_ratio': trade_mon_per
    #         }
    #         result = {
    #             'jytj': statistics,
    #             'jyzb': index
    #         }
    #     else:
    #         result = []

        # return result






